﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CNotebook.Inventory;

namespace CNotebook.Sales
{
    public class InvoiceManager:ConnectionManager
    {
        #region Others

        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OINV WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool IsReferenceSIDRNoExists(string _DOCUMNT_CODE, string _DIVREFERENCE)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OINV WHERE DOCUMENTCODE=@DocumentCode AND DivReference=@DivReference";
            oCommand.Parameters.Add(new SqlParameter("@DocumentCode", _DOCUMNT_CODE));
            oCommand.Parameters.Add(new SqlParameter("@DivReference", _DIVREFERENCE));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        #endregion

        #region Adding/Updating Invoice
        public SqlException AddInvoice(InvoiceUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO OINV (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, StoreCode, CardCode, CardName, ContactPerson, PaymentTerms, Reference, CustRefPO, BillTo, ShipTo, SalesMan, Remarks, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, WithHoldingTax, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount, Division, DocumentCode, DivReference, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                                            " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @StoreCode, @CardCode, @CardName, @ContactPerson, @PaymentTerms, @Reference, @CustRefPO, @BillTo, @ShipTo, @SalesMan, @Remarks, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @WithHoldingTax, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount, @Division, @DocumentCode, @DivReference, GetDate(), @CreatedBy, GetDate(), @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
                oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
                oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
                oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
                oCommand.Parameters.Add(new SqlParameter("@DocumentCode", oUnit.DocumentCode));
                oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));



                oCommand.ExecuteNonQuery();

                #region Line Items
                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO INV1 (DocNum, ItemCode, ItemDesc, UoM, Qty, CQty, LotNumber, PriceListCode, UnitPrice, UnitCost, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuID) VALUES " +
                                                                        "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @CQty, @LotNumber, @PriceListCode, @UnitPrice, @UnitCost, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuID)";



                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));

                        oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"].ToString())));


                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }

                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemGuID", oRow["ItemGuID"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }

                }
                #endregion


                return null;
            }
            catch(SqlException ex)
            {
                return ex;
            }

        }

        public SqlException UpdateInvoice(InvoiceUnit oUnit)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OINV SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, StoreCode=@StoreCode, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, PaymentTerms=@PaymentTerms, Reference=@Reference, CustRefPO=@CustRefPO, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, WithHoldingTax=@WithHoldingTax, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, DivReference=@DivReference, DocumentCode=@DocumentCode, Division=@Division, ModifiedBy=@ModifiedBy, DateModified=GetDate()" +
                                                            " WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));

                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
                oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));

                oCommand.Parameters.Add(new SqlParameter("@DocumentCode", oUnit.DocumentCode));
                oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                #region Line Items
                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {

                    if (oRow.RowState == DataRowState.Added)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO INV1 (DocNum, ItemCode, ItemDesc, UoM, QtyOrder, CQtyOrder, Qty, CQty, PriceListCode, UnitPrice, UnitCost, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @QtyOrder, @CQtyOrder, @Qty, @CQty, @PriceListCode, @UnitPrice, @UnitCost, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";

                        oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        if (oRow["Qty"] == DBNull.Value)
                        {
                            int _ZeroValue = 0;
                            oRow["Qty"] = _ZeroValue;
                        }
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));

                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"].ToString())));
                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();

                    }


                    if (oRow.RowState == DataRowState.Modified)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "UPDATE INV1 set ItemCode=@ItemCode, ItemDesc=@ItemDesc, UoM=@UoM, Qty=@Qty, CQty=@CQty, PriceListCode=@PriceListCode, UnitPrice=@UnitPrice, UnitCost=@UnitCost, IsFree=@IsFree, DiscPrcnt=@DiscPrcnt, Discount=@Discount, TotalLineDisc=@TotalLineDisc, Amount=@Amount WHERE " +
                                                                    "ItemGuid=@ItemGuid)";

                        oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        if (oRow["Qty"] == DBNull.Value)
                        {
                            int _ZeroValue = 0;
                            oRow["Qty"] = _ZeroValue;
                        }
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"].ToString())));
                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();

                    }

                }

                #endregion

                #region Updating Accounting Entries


                oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "DELETE [INV3] WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                if (oUnit.AccountingEntries != null)
                {
                    foreach (DataRow oRow in oUnit.AccountingEntries.Rows)
                    {
                        if (oRow.RowState != DataRowState.Deleted)
                        {
                            oCommand = new SqlCommand();

                            oCommand.Connection = this.Connection;
                            oCommand.CommandType = CommandType.Text;

                            oCommand.CommandText = "INSERT INTO [INV3] (DocNum, AcctCode, AcctName, Particulars, DebitAmt, CreditAmt) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @DebitAmt, @CreditAmt)";

                            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                            oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@DebitAmt", oRow["DebitAmt"].ToString()));
                            oCommand.Parameters.Add(new SqlParameter("@CreditAmt", oRow["CreditAmt"].ToString()));

                            oCommand.ExecuteNonQuery();
                        }
                    }
                }
                



                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }

        }

        public SqlException AddDistributionEntries(string DocumentNumber, DataTable dtAccountingEntries)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                #region Accounting Entries


                oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "DELETE [INV3] WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNumber));
                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in dtAccountingEntries.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;
                        oCommand.CommandType = CommandType.Text;

                        oCommand.CommandText = "INSERT INTO [INV3] (DocNum, AcctCode, AcctName, Particulars, DebitAmt, CreditAmt) VALUES (@DocNum, @AcctCode, @AcctName, @Particulars, @DebitAmt, @CreditAmt)";
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DebitAmt", oRow["DebitAmt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CreditAmt", oRow["CreditAmt"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }
                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }
        
        public void SetAppliedAmount(string DocumentNo, string _AMOUNT)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OINV SET AppliedAmount=AppliedAmount+@AppliedAmount WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", DocumentNo));
            oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", _AMOUNT.Replace(",","")));

            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Post/Cancel/Close Invoice
        public SqlException SetInvoiceInProcess(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OINV SET DOCSTATUS='In-Process' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        public void PostDocument(string _DOCNUMBER, string PostedBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OINV SET DOCSTATUS='Posted', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", PostedBy));
            oCommand.ExecuteNonQuery();


        }

        public void DeliverOrder(string _WAREHOUSE, string _SALES_ORDER_NO, string _DOCNUMBER, string DeliveredBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OINV SET DOCSTATUS='Delivered', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", DeliveredBy));
            oCommand.ExecuteNonQuery();


            #region Applying Deduction to Inventory
            string selectSQL = "SELECT * FROM INV2 WHERE DocNum=@DocNum";


            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = selectSQL;
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                StockManager oManager = new StockManager();
                oManager.Connection = this.Connection;

                if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                {
                    oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), (Convert.ToDouble(oRow["QtySrvd"]) * -1).ToString());
                }
                else
                {
                    WarehouseManager oWhs = new WarehouseManager();
                    oWhs.ConnectionString = this.ConnectionString;
                    oWhs.Open();
                    DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                    oWhs.Close();
                    oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemDesc"].ToString(), (Convert.ToDouble(oRow["Qty"]) * -1).ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString());
                }
            }
            #endregion

            #region Updating Sales Order Remarks

            if (_SALES_ORDER_NO.Trim() != "")
            {
                #region Getting the Line Items of SalesOrder
                OrderManager oSOManager = new OrderManager();
                oSOManager.Connection = this.Connection;
                DataTable dtSO = new DataTable();

              
                dtSO = oSOManager.GetOrderLineItems(_SALES_ORDER_NO);

                #endregion

                int iStatus = 2; // 1 = Open, 2 = Closed
                
                foreach (DataRow oRow in dtSO.Rows)
                {
                    oCommand = new SqlCommand();

                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "SELECT CASE WHEN SUM(Qty) IS NULL THEN 0 ELSE SUM(Qty) END Qty FROM OINV JOIN INV1 ON OINV.DOCNUM = INV1.DOCNUM WHERE INV1.ItemGuid = @ItemGuid AND (OINV.DOCSTATUS <> 'Draft' or OINV.DocStatus <> 'Canceled')";

                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                    DataTable dtINV2 = new DataTable();
                    SqlDataAdapter dAdapter = new   SqlDataAdapter();
                    dAdapter.SelectCommand  = oCommand;
                    dAdapter.Fill(dtINV2);

                    if (dtINV2.Rows.Count > 0)
                    {
                        DataRow drRowINV2 = dtINV2.Rows[0];

                        if (Convert.ToDouble(oRow["Qty"]) != Convert.ToDouble(drRowINV2["Qty"]))
                        {
                            iStatus = 1;
                            break;
                        }
                    }
                    else
                    {
                        iStatus = 1;
                        break;
                    }
                    



                }

                if (iStatus == 2)
                {
                    oSOManager.CloseOrder(_SALES_ORDER_NO);
                }


                if (iStatus == 1)
                {
                    oSOManager.SetOrderOpen(_SALES_ORDER_NO);
                }


            }

            
            #endregion

        }

        public void CancelOrder(string sOrderNo, string CancelledBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OINV SET DOCSTATUS='Canceled', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", CancelledBy));

            oCommand.ExecuteNonQuery();
        }

        public void CloseOrder(string _INVOICE_NO, string ClosedBy)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OINV SET DOCSTATUS='Closed', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _INVOICE_NO));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", ClosedBy));
            oCommand.ExecuteNonQuery();
        }

        public void SetOrderOpen(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OINV SET DOCSTATUS='Open' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
            oCommand.ExecuteNonQuery();
        }

        #endregion

        #region Retreiving

        public DataTable GetAccountingDistribution(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM INV3 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
       
        // Getting Invoices - Search
       
        public DataTable GetInvoices(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE DocNum LIKE @DOCNUM ORDER BY RecNo DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesByRefSONo(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE Reference LIKE @DOCNUM ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesBySIDRNo(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE DivReference LIKE @DOCNUM ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesByCustRefPO(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE CustRefPO LIKE @DOCNUM ORDER BY CustRefPO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        
        public DataTable GetInvoicesByBranchName(string sCustomerName)
        {
            sCustomerName = sCustomerName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE CardName LIKE @DOCNUM ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sCustomerName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetInvoicesByBranchCode(string _CUST_CODE)
        {
            _CUST_CODE = _CUST_CODE.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE CardCode LIKE @CardCode ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CUST_CODE));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        // Getting Invoices - Search with Balance

        public DataTable GetInvoicesWithBalance(string OutletCode, string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE (NetAmount - AppliedAmount) > 0 AND DocNum LIKE @DOCNUM AND DOCSTATUS='Posted' AND CardCode=@CardCode ORDER BY RecNo DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OutletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesBySIDRNoWithBalance(string OutletCode, string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE (NetAmount - AppliedAmount) > 0 AND DivReference LIKE @DOCNUM AND DOCSTATUS='POSTED'  AND CardCode=@CardCode ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OutletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesByCustRefPOWithBalance(string OutletCode, string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE (NetAmount - AppliedAmount) > 0 AND CustRefPO LIKE @DOCNUM  AND DOCSTATUS='POSTED'  AND CardCode=@CardCode ORDER BY CustRefPO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", OutletCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesWithBalance(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE (NetAmount - AppliedAmount) > 0 AND DocNum LIKE @DOCNUM AND DOCSTATUS='Posted' ORDER BY RecNo DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesBySIDRNoWithBalance(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE (NetAmount - AppliedAmount) > 0 AND DivReference LIKE @DOCNUM AND DOCSTATUS='POSTED' ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesByCustRefPOWithBalance(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE (NetAmount - AppliedAmount) > 0 AND CustRefPO LIKE @DOCNUM  AND DOCSTATUS='POSTED' ORDER BY CustRefPO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoicesByBranchWithBalance(string sCustomerName)
        {
            sCustomerName = sCustomerName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE (NetAmount - AppliedAmount) > 0 AND CardName LIKE @DOCNUM  AND DOCSTATUS='POSTED' ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sCustomerName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetInvoicesByBranchCodeWithBalance(string _CUST_CODE)
        {
            _CUST_CODE = _CUST_CODE.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE (NetAmount - AppliedAmount) > 0 AND CardCode LIKE @CardCode  AND DOCSTATUS='POSTED' ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CUST_CODE));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        //---------------------------------------------------------------------

        public DataRow GetInvoice(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, NetAmount - AppliedAmount TotalBalance FROM OINV WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetInvoiceLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM INV1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        public DataTable GetIssuedLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM INV2 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }
        #endregion

        #region Report Retreivable

        public DataTable GetAgingInvoices(int IsByDeliveryDate, DateTime _ASOF)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_AGING_REPORT";
            oCommand.Parameters.Add(new SqlParameter("@ASOF", _ASOF));
            oCommand.Parameters.Add(new SqlParameter("@ByDeliveryDate", IsByDeliveryDate));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        //--------------------------------------------------------------------------
        // HEADERS

        public DataTable GetInvoiceLogsByDocument(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_SI";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        
        public DataTable GetInvoiceLogsByItems(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_SI_PER_ITEM";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }


        //--------------------------------------------------------------------------
        // LINE ITEMS


        public DataTable GetInvoiceByDeliveryDate(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_DR_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoiceStatusByPostingDate(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_DR_STATUS_BY_POSTINGDATE";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));
            
            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetInvoiceStatusByInvoiceDate(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_DR_STATUS_BY_INVOICEDATE";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }


        #endregion
    }
}

